
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 10/06/2014 08:41:49
-- Generated from EDMX file: C:\Users\qwerty\documents\visual studio 2013\Projects\RFBR\RFBR.DataAccess\DB.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [RFBR];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ScientistScientistWorkLink]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScientistInterests] DROP CONSTRAINT [FK_ScientistScientistWorkLink];
GO
IF OBJECT_ID(N'[dbo].[FK_ScientificWorkScientistWorkLink]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScientistInterests] DROP CONSTRAINT [FK_ScientificWorkScientistWorkLink];
GO
IF OBJECT_ID(N'[dbo].[FK_ScientistScientistsPublications]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScientistsPublications] DROP CONSTRAINT [FK_ScientistScientistsPublications];
GO
IF OBJECT_ID(N'[dbo].[FK_PublicationScientistsPublications]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScientistsPublications] DROP CONSTRAINT [FK_PublicationScientistsPublications];
GO
IF OBJECT_ID(N'[dbo].[FK_ScientistJobs]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Jobs] DROP CONSTRAINT [FK_ScientistJobs];
GO
IF OBJECT_ID(N'[dbo].[FK_OrganisationJobs]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Jobs] DROP CONSTRAINT [FK_OrganisationJobs];
GO
IF OBJECT_ID(N'[dbo].[FK_KnowledgeBaseFrame]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Frames] DROP CONSTRAINT [FK_KnowledgeBaseFrame];
GO
IF OBJECT_ID(N'[dbo].[FK_FrameSlot]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Slots] DROP CONSTRAINT [FK_FrameSlot];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Scientists]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Scientists];
GO
IF OBJECT_ID(N'[dbo].[Organisations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Organisations];
GO
IF OBJECT_ID(N'[dbo].[InterestsCodes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[InterestsCodes];
GO
IF OBJECT_ID(N'[dbo].[ScientistInterests]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScientistInterests];
GO
IF OBJECT_ID(N'[dbo].[Publications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Publications];
GO
IF OBJECT_ID(N'[dbo].[ScientistsPublications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScientistsPublications];
GO
IF OBJECT_ID(N'[dbo].[Jobs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Jobs];
GO
IF OBJECT_ID(N'[dbo].[Frames]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Frames];
GO
IF OBJECT_ID(N'[dbo].[Slots]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Slots];
GO
IF OBJECT_ID(N'[dbo].[KnowledgeBases]', 'U') IS NOT NULL
    DROP TABLE [dbo].[KnowledgeBases];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Scientists'
CREATE TABLE [dbo].[Scientists] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [FatherName] nvarchar(max)  NOT NULL,
    [Grade] nvarchar(max)  NOT NULL,
    [BirthDate] datetime  NOT NULL,
    [Sex] int  NOT NULL,
    [OrganisationId] nvarchar(max)  NOT NULL,
    [Post] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [PublicationsCount] nvarchar(max)  NOT NULL,
    [Education] nvarchar(max)  NOT NULL,
    [Status] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Organisations'
CREATE TABLE [dbo].[Organisations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [ShortName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'InterestsCodes'
CREATE TABLE [dbo].[InterestsCodes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Code] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ScientistInterests'
CREATE TABLE [dbo].[ScientistInterests] (
    [ScientistId] int  NOT NULL,
    [InterestsId] int  NOT NULL
);
GO

-- Creating table 'Publications'
CREATE TABLE [dbo].[Publications] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ScientistsPublications'
CREATE TABLE [dbo].[ScientistsPublications] (
    [ScientistId] int  NOT NULL,
    [PublicationId] int  NOT NULL
);
GO

-- Creating table 'Jobs'
CREATE TABLE [dbo].[Jobs] (
    [ScientistId] int  NOT NULL,
    [OrganisationId] int  NOT NULL
);
GO

-- Creating table 'Frames'
CREATE TABLE [dbo].[Frames] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ParentId] int  NULL,
    [KnowledgeBaseId] int  NOT NULL
);
GO

-- Creating table 'Slots'
CREATE TABLE [dbo].[Slots] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Type] int  NOT NULL,
    [FrameId] int  NOT NULL,
    [Value] nvarchar(max)  NOT NULL,
    [Inheritance] int  NOT NULL
);
GO

-- Creating table 'KnowledgeBases'
CREATE TABLE [dbo].[KnowledgeBases] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Scientists'
ALTER TABLE [dbo].[Scientists]
ADD CONSTRAINT [PK_Scientists]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Organisations'
ALTER TABLE [dbo].[Organisations]
ADD CONSTRAINT [PK_Organisations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'InterestsCodes'
ALTER TABLE [dbo].[InterestsCodes]
ADD CONSTRAINT [PK_InterestsCodes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ScientistId], [InterestsId] in table 'ScientistInterests'
ALTER TABLE [dbo].[ScientistInterests]
ADD CONSTRAINT [PK_ScientistInterests]
    PRIMARY KEY CLUSTERED ([ScientistId], [InterestsId] ASC);
GO

-- Creating primary key on [Id] in table 'Publications'
ALTER TABLE [dbo].[Publications]
ADD CONSTRAINT [PK_Publications]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ScientistId], [PublicationId] in table 'ScientistsPublications'
ALTER TABLE [dbo].[ScientistsPublications]
ADD CONSTRAINT [PK_ScientistsPublications]
    PRIMARY KEY CLUSTERED ([ScientistId], [PublicationId] ASC);
GO

-- Creating primary key on [ScientistId], [OrganisationId] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [PK_Jobs]
    PRIMARY KEY CLUSTERED ([ScientistId], [OrganisationId] ASC);
GO

-- Creating primary key on [Id] in table 'Frames'
ALTER TABLE [dbo].[Frames]
ADD CONSTRAINT [PK_Frames]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Slots'
ALTER TABLE [dbo].[Slots]
ADD CONSTRAINT [PK_Slots]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'KnowledgeBases'
ALTER TABLE [dbo].[KnowledgeBases]
ADD CONSTRAINT [PK_KnowledgeBases]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ScientistId] in table 'ScientistInterests'
ALTER TABLE [dbo].[ScientistInterests]
ADD CONSTRAINT [FK_ScientistScientistWorkLink]
    FOREIGN KEY ([ScientistId])
    REFERENCES [dbo].[Scientists]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [InterestsId] in table 'ScientistInterests'
ALTER TABLE [dbo].[ScientistInterests]
ADD CONSTRAINT [FK_ScientificWorkScientistWorkLink]
    FOREIGN KEY ([InterestsId])
    REFERENCES [dbo].[InterestsCodes]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScientificWorkScientistWorkLink'
CREATE INDEX [IX_FK_ScientificWorkScientistWorkLink]
ON [dbo].[ScientistInterests]
    ([InterestsId]);
GO

-- Creating foreign key on [ScientistId] in table 'ScientistsPublications'
ALTER TABLE [dbo].[ScientistsPublications]
ADD CONSTRAINT [FK_ScientistScientistsPublications]
    FOREIGN KEY ([ScientistId])
    REFERENCES [dbo].[Scientists]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [PublicationId] in table 'ScientistsPublications'
ALTER TABLE [dbo].[ScientistsPublications]
ADD CONSTRAINT [FK_PublicationScientistsPublications]
    FOREIGN KEY ([PublicationId])
    REFERENCES [dbo].[Publications]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PublicationScientistsPublications'
CREATE INDEX [IX_FK_PublicationScientistsPublications]
ON [dbo].[ScientistsPublications]
    ([PublicationId]);
GO

-- Creating foreign key on [ScientistId] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [FK_ScientistJobs]
    FOREIGN KEY ([ScientistId])
    REFERENCES [dbo].[Scientists]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [OrganisationId] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [FK_OrganisationJobs]
    FOREIGN KEY ([OrganisationId])
    REFERENCES [dbo].[Organisations]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OrganisationJobs'
CREATE INDEX [IX_FK_OrganisationJobs]
ON [dbo].[Jobs]
    ([OrganisationId]);
GO

-- Creating foreign key on [KnowledgeBaseId] in table 'Frames'
ALTER TABLE [dbo].[Frames]
ADD CONSTRAINT [FK_KnowledgeBaseFrame]
    FOREIGN KEY ([KnowledgeBaseId])
    REFERENCES [dbo].[KnowledgeBases]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_KnowledgeBaseFrame'
CREATE INDEX [IX_FK_KnowledgeBaseFrame]
ON [dbo].[Frames]
    ([KnowledgeBaseId]);
GO

-- Creating foreign key on [FrameId] in table 'Slots'
ALTER TABLE [dbo].[Slots]
ADD CONSTRAINT [FK_FrameSlot]
    FOREIGN KEY ([FrameId])
    REFERENCES [dbo].[Frames]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FrameSlot'
CREATE INDEX [IX_FK_FrameSlot]
ON [dbo].[Slots]
    ([FrameId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------